package oa.game;

import java.io.Closeable;
import java.sql.*;
import java.util.Map;

public class DBUtils implements Closeable {

    private final Connection conn;
    private final PreparedStatement existeJuegoStatement;
    private final PreparedStatement getIdJugadorStatement;
    private final PreparedStatement getIdArmaStatement;
    private final PreparedStatement insertMuerteStatement;
    private final PreparedStatement insertJuegoStatement;
    private final PreparedStatement insertParametroStatement;
    private final PreparedStatement insertPuntosJugador;
    private final PreparedStatement insertEventoBandera;
    private final PreparedStatement insertEquipo;

    public DBUtils() throws SQLException {
        this.conn = MySQLConnector.getConnection();
        this.existeJuegoStatement = conn.prepareStatement("select count(game_id) as cnt from game_parameters where name = ? and value = ?");
        this.getIdJugadorStatement = conn.prepareStatement("select id from player_nicks where nick = ?");
        this.getIdArmaStatement = conn.prepareStatement("select id from weapon_nicks where nick = ?");
        this.insertMuerteStatement = conn.prepareStatement("insert into game_kills (game_id,killer_id,victim_id,weapon_id,created_at,updated_at) values (?,?,?,?,?,?)");
        this.insertJuegoStatement = conn.prepareStatement("insert into games (duration,winner,created_at,updated_at) values (?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
        this.insertParametroStatement = conn.prepareStatement("insert into game_parameters (game_id,name,value,created_at,updated_at) values (?,?,?,?,?)");
        this.insertPuntosJugador = conn.prepareStatement("insert into game_points (game_id,player_id,time,points,created_at,updated_at) values (?,?,?,?,?,?)");
        this.insertEventoBandera = conn.prepareStatement("insert into game_flag_events (game_id,action,team,time,player_id,`index`,created_at,updated_at) values (?,?,?,?,?,?,?,?)");
        this.insertEquipo = conn.prepareStatement("insert into game_teams (game_id,player_id,team,created_at,updated_at) values (?,?,?,?,?)");
    }

    @Override
    public void close() {
        MyUtils.silentClose(insertEquipo);
        MyUtils.silentClose(insertEventoBandera);
        MyUtils.silentClose(insertPuntosJugador);
        MyUtils.silentClose(getIdArmaStatement);
        MyUtils.silentClose(insertJuegoStatement);
        MyUtils.silentClose(insertParametroStatement);
        MyUtils.silentClose(getIdJugadorStatement);
        MyUtils.silentClose(insertMuerteStatement);
        MyUtils.silentClose(existeJuegoStatement);
        MyUtils.silentClose(conn);
    }

    public boolean existeJuego(Map<String, String> parametrosJuego) throws SQLException {

        ResultSet rs = null;
        try {
            String fechaJuego = parametrosJuego.get("g_timestamp");

            existeJuegoStatement.setString(1, "g_timestamp");
            existeJuegoStatement.setString(2, fechaJuego);

            rs = existeJuegoStatement.executeQuery();

            if (rs.next()) {
                int count = rs.getInt("cnt");

                return count > 0;
            }

            throw new SQLException("Could not run count query");
        } finally {
            MyUtils.silentClose(rs);
        }
    }

    public int getIdJugador(String apodoKiller) throws SQLException {
        ResultSet rs = null;
        try {
            getIdJugadorStatement.setString(1, apodoKiller);

            rs = getIdJugadorStatement.executeQuery();

            if (rs.next()) {
                return rs.getInt("id");
            } else {
                return -1;
            }
        } finally {
            MyUtils.silentClose(rs);
        }
    }

    public int getIdArma(String apodoArma) throws SQLException {
        ResultSet rs = null;
        try {
            getIdArmaStatement.setString(1, apodoArma);

            rs = getIdArmaStatement.executeQuery();

            if (rs.next()) {
                return rs.getInt("id");
            } else {
                return -1;
            }
        } finally {
            MyUtils.silentClose(rs);
        }
    }

    public void crearMuerte(long gameId, int idKiller, int idVictim, int idArma) throws SQLException {
        insertMuerteStatement.setLong(1, gameId);
        insertMuerteStatement.setInt(2, idKiller);
        insertMuerteStatement.setInt(3, idVictim);
        insertMuerteStatement.setInt(4, idArma);
        insertMuerteStatement.setDate(5, new Date(new java.util.Date().getTime()));
        insertMuerteStatement.setDate(6, new Date(new java.util.Date().getTime()));
        insertMuerteStatement.executeUpdate();
    }

    public int crearJuego(int duracion, String equipoGanador) throws SQLException {

        insertJuegoStatement.setInt(1, duracion);
        insertJuegoStatement.setString(2, equipoGanador);
        insertJuegoStatement.setDate(3, new Date(new java.util.Date().getTime()));
        insertJuegoStatement.setDate(4, new Date(new java.util.Date().getTime()));
        insertJuegoStatement.executeUpdate();

        ResultSet rs = insertJuegoStatement.getGeneratedKeys();
        if (rs.next()) {
            int gameId = rs.getInt(1);
            return gameId;
        } else {
            throw new SQLException("game id not returned");
        }
    }

    public void crearParametro(long gameId, String key, String value) throws SQLException {
        insertParametroStatement.setLong(1, gameId);
        insertParametroStatement.setString(2, key);
        insertParametroStatement.setString(3, value);
        insertParametroStatement.setDate(4, new Date(new java.util.Date().getTime()));
        insertParametroStatement.setDate(5, new Date(new java.util.Date().getTime()));
        insertParametroStatement.executeUpdate();
    }

    public void crearPuntosJugador(long gameId, int idPlayer, int puntos, int time) throws SQLException {
        insertPuntosJugador.setLong(1, gameId);
        insertPuntosJugador.setInt(2, idPlayer);
        insertPuntosJugador.setInt(3, time);
        insertPuntosJugador.setInt(4, puntos);
        insertPuntosJugador.setDate(5, new Date(new java.util.Date().getTime()));
        insertPuntosJugador.setDate(6, new Date(new java.util.Date().getTime()));
        insertPuntosJugador.executeUpdate();
    }

    public void crearEvento(long gameId, AccionesBandera accion, String equipo, Integer segundos, Integer jugador, int index) throws SQLException {
        insertEventoBandera.setLong(1, gameId);
        insertEventoBandera.setString(2, accion.toString());
        insertEventoBandera.setString(3, equipo);
        insertEventoBandera.setInt(4, segundos);
        insertEventoBandera.setInt(5, jugador);
        insertEventoBandera.setInt(6, index);
        insertEventoBandera.setDate(7, new Date(new java.util.Date().getTime()));
        insertEventoBandera.setDate(8, new Date(new java.util.Date().getTime()));
        insertEventoBandera.executeUpdate();
    }

    public void crearEquipo(long gameId, String team, Integer jugador) throws SQLException {
        insertEquipo.setLong(1, gameId);
        insertEquipo.setInt(2, jugador);
        insertEquipo.setString(3, team);
        insertEquipo.setDate(4, new Date(new java.util.Date().getTime()));
        insertEquipo.setDate(5, new Date(new java.util.Date().getTime()));
        insertEquipo.executeUpdate();
    }
}
